<?php 
/**
 * Registration controller class for Users.
 */
define('COMPANY_JOBTEST_BASEDIR', JPATH_ROOT."/images/jobs/jobtest");

class PJobpositionModelPosition extends JModelLegacy 
{
   public function showInterviews($userid, $jobid, $caching=true) {
      $db = PFactory::getDbo();
      $query = 'SELECT DISTINCT i.expected_answer, q.*, u.unit_name, (SELECT COUNT(*) FROM `*PREFIX*hr_company_interview` WHERE job_id='.intval($jobid).') as ci_count'.
               ' FROM `*PREFIX*hr_interview_questions` as q '.
               ' left join `*PREFIX*hr_company_interview` as i on i.interview_id=q.id and i.job_id='.intval($jobid).
               ' left join `*PREFIX*hr_interview_questions_unit` as u on u.id=q.unit_id '.
               ' where q.company_id in ('.intval($userid).', 0) ORDER BY i.id';

      // if caching is not needed, get data directly 
      if(!$caching)
         return $this->_showInterviews($query);

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_jobpos');
      // get cached data now 
      $data = $cache->call(array($this, '_showInterviews'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      // we use company id, so changes will be triggered for all company users 
      CacheHelper::setTriggers(intval($userid), $cache, array('*PREFIX*hr_company_interview-'.intval($jobid)));

      return $data;
   }

   public function _showInterviews($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function showPersontests($userid, $jobid) {
      $db = PFactory::getDbo();
      $query = 'select ps.*, pj.jobid '.
               'from #__persontest_sheet as ps '.
               'left join #__persontest_jobsheet as pj on ps.id=pj.sheetid and pj.jobid='.intval($jobid).' '.
               'where published=1';
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }

   public function getSheetDetails($sheetid) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      $query =  'SELECT js.id, js.totaltime, js.questioncount, js.title, js.published, js.hits '.
               'from #__jobtest_sheet as js '.
               'where js.id='.intval($sheetid).' and js.userid_creator='.intval($user->id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }
   
   public function saveJobDemands() {
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();

      $job_id = JRequest::getInt('job_id');

      $interview_ids = JRequest::getVar('interview_id', array(), '', 'array');
      $lang_id = JRequest::getVar('lang_name', array(), '', 'array');
      $lang_level = JRequest::getVar('lang_level', array(), '', 'array');
      $skill_name = JRequest::getVar('skill_name', array(), '', 'array');
      $skill_level = JRequest::getVar('skill_level', array(), '', 'array');

      foreach($lang_id as $k=>$v){
         if($v!=''){
            $interview_ids[$v] = $lang_level[$k];
         }
      }
         
      // reset company interview of language
      $query = 'select group_concat(id) as interview_id from `*PREFIX*hr_interview_questions` where alias="LANG"';
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $langs = $db->loadResult();
      if($langs){
         $query = 'delete from `*PREFIX*hr_company_interview` where interview_id in ('.$langs.')  and job_id='.intval($job_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }
      
      $queries = $delete_basic_demands = $postinterviewid = array();

      if($skill_name){
         $queries_skill = array();
         $jsonchoices = json_encode(array("1"=>"了解","2"=>"普通","3"=>"熟练","4"=>"精通"));
         foreach($skill_name as $k=>$v){
            array_push($queries_skill, "('SKILL', ".intval($user->id).", 0, ".$db->quote($k).", ".$db->quote($k).", ".$db->quote($jsonchoices).", 2, 'GREATER', UTC_TIMESTAMP())");
         }

         $query = 'insert into `*PREFIX*hr_interview_questions` (alias, company_id, public, text_hr, text_jobseeker,choices, unit_id, match_rule, updated) values '.
                        implode(',', $queries_skill).
                        ' ON DUPLICATE KEY UPDATE updated=values(updated),choices=values(choices)';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();

         $query = 'select id, text_hr from `*PREFIX*hr_interview_questions` where alias="SKILL" and company_id='.intval($user->id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $hasskills = $db->loadAssocList();

         foreach($hasskills as $j){
            $original_skills_id[] = $j['id'];
         }

         $skillanswer = array();

         foreach($skill_name as $k=>$v){
            if(!empty($v)){
               foreach($hasskills as $j){
                  if(in_array($k, $j)){
                     foreach($skill_level as $key=>$value){
                     	if(empty($value)){
                     	   $this->setError('技能'.$key.'的熟练度为必选项！');
                     	   return false;
                     	}elseif($key == $k){
                           $skillanswer['skill'] = $key;
                           $skillanswer['level'] = $value;
                           $skillanswer['exp'] = $v;
                           $skillanswerjson = json_encode($skillanswer);
                           $now_skills_id[] = $j['id'] ? $j['id'] : false;
                           array_push($queries, "(".intval($job_id).", ".intval($j['id']).", ".$db->quote($skillanswerjson).", UTC_TIMESTAMP())");
                        }

                     }
                  }
               }
            }
         }

         $delskills = array_diff($original_skills_id, $now_skills_id);
      } else {
         $query = 'select id, text_hr from `*PREFIX*hr_interview_questions` where alias="SKILL" and company_id='.intval($user->id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $hasskills = $db->loadAssocList();
         
         if(!empty($hasskills)){
            foreach($hasskills as $j){
               $delskills[] = $j['id'];
            }
         }
      }

      if(!empty($delskills)){
         $query = 'delete from `*PREFIX*hr_company_interview` where interview_id in ('.implode(',', $delskills).') and job_id='.intval($job_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }

      foreach($interview_ids as $k=>$v){
      	 array_push($postinterviewid,intval($k));
         if(!empty($v)){
            if(is_array($v)){
               if(!empty($v['state']) && !empty($v['city'])){
                  array_push($queries, "(".intval($job_id).", ".intval($k).", ".$db->quote($v['state']."-".$v['city']).",UTC_TIMESTAMP())");
               } elseif(!empty($v['age_1']) && !empty($v['age_2'])){
                  if($v['age_1'] <= $v['age_2']){
                     array_push($queries, "(".intval($job_id).", ".intval($k).", ".$db->quote($v['age_1']."-".$v['age_2']).",UTC_TIMESTAMP())");
                  } else {
                     array_push($queries, "(".intval($job_id).", ".intval($k).", ".$db->quote($v['age_1']."-".$v['age_2']).",UTC_TIMESTAMP())");
                  }
               } else {
                  array_push($delete_basic_demands, intval($k));
               }
            } else {
               array_push($queries, "(".intval($job_id).", ".intval($k).", ".$db->quote($v).",UTC_TIMESTAMP())");
            }
         } else {
            array_push($delete_basic_demands, intval($k));
         }
      }

      //delete basic demands if set '不限'
      if(!empty($delete_basic_demands)){
         $query = 'delete from `*PREFIX*hr_company_interview` where interview_id in ('.implode(',', $delete_basic_demands).') and job_id='.intval($job_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();  
      }

      if(!empty($queries)){
         $query = 'insert into `*PREFIX*hr_company_interview` (job_id, interview_id, expected_answer, chosen_time) values '.
                   implode(',', $queries).
                   ' ON DUPLICATE KEY UPDATE expected_answer=values(expected_answer), chosen_time=values(chosen_time)';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }

      // update school level
      if($interview_ids[1] > 4){
         $query = 'insert into `*PREFIX*hr_company_interview` (job_id, interview_id, expected_answer, chosen_time) values '.
                   '('.intval($job_id).', 8, "", UTC_TIMESTAMP())'.
                   ' ON DUPLICATE KEY UPDATE expected_answer=values(expected_answer), chosen_time=values(chosen_time)';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      } elseif($interview_ids[1] == '') {
         $query = 'delete from `*PREFIX*hr_company_interview` where interview_id=8 and job_id='.intval($job_id);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }

      $query = 'select count(*) from `*PREFIX*hr_company_interview` where job_id='.intval($job_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $count = $db->loadResult();
      if(empty($count)){
         $this->setError('请至少选择一项');
         return false;
      }

      // update job requirement 
      JobHelper::setJobRequirements($job_id, 'onlineinterview');
      // current company' num_job_matched
      $query="UPDATE #__jobs_employer SET num_job_matched=(SELECT COUNT(id) FROM #__jobs_job WHERE LOCATE('onlineinterview',online_interview_requirements)>0 AND system=0 AND employer_id=".intval($user->id).") WHERE user_id=".intval($user->id);
      $db->setQuery($query);
      $db->execute();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobs_job','*PREFIX*hr_company_interview', '*PREFIX*hr_company_interview-'.intval($job_id)));

      // add a task to re-calculate match result 
      DelayTaskJob::add(
         array('summary'=>'1个职位重新计算匹配度',
               'userid'=>PFactory::getMasterUser()->get('id'), 
               'owner'=>OC_User::getUser(),
               'handler'=>'/usr/bin/php ' . realpath(JPATH_SITE . '/../cloud/cli/resumematch.php'),
               'parameters'=>json_encode(array($job_id)),
               'hidden'=>true, 
               'unique'=> true   // only one same task allowed at a time 
              )
         );

      return true;

   }


   public function savepersontest() {
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      $post = JRequest::get('post');
      $db = PFactory::getDbo();
      $job_id = $post['job_id'];

      $persontestsheetid = $post['persontestsheet'];
      if(!empty($persontestsheetid)){
         $query = 'insert into #__persontest_jobsheet (jobid, sheetid) values ('.intval($job_id).', '.intval($persontestsheetid).') on duplicate key update sheetid=values(sheetid)';
      } else {
         $query = 'delete from #__persontest_jobsheet where jobid='.intval($job_id);
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();         

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobs_job', '#__jobs_job-'.intval($job_id), '#__persontest_jobsheet', '#__persontest_jobsheet-'.intval($persontestsheetid)));
      JobHelper::setJobRequirements($job_id, 'persontest');         
      // add a task to update all jobseekers' rusumes online interview progress
      DelayTaskJob::add(
         array('summary'=>'1个职位更新简历性格测试结果',
               'userid'=>PFactory::getMasterUser()->get('id'), 
               'owner'=>OC_User::getUser(),
               'handler'=>'/usr/bin/php ' . realpath(JPATH_SITE . '/../cloud/cli/persontestupdate.php'),
               'parameters'=>json_encode(array($job_id)),
               'hidden'=>true, 
               'unique'=> true   // only one same task allowed at a time 
              )
      );
      return true;
   }

   public function saveSheet() {
      if(!PSession::checkToken('post')) {
         $this->setError('非法的系统安全码');
         return false;
      }
      $post = JRequest::get('post');
      $db = PFactory::getDbo();

      $job_id = $post['job_id'];
      $sheet_id = $post['sheet_id'];
      
      if (empty($post['title']) || empty($post['totaltime']) || is_int($post['totaltime'])) {
         $this->setError('请填写标题和时间！');
         return false;
      }

      $title_len = JString::strlen(trim($post['title']));
      if ($title_len > 20 || $title_len < 5) {
         $this->setError('请输入5~20个字符！');
         return false;
      }

      $user = PFactory::getMasterUser();
      if (!empty($sheet_id)) {
         $query = "UPDATE #__jobtest_sheet SET title = ".$db->quote($post['title']).",totaltime = ".intval($post['totaltime']).",modified = UTC_TIMESTAMP,userid_modifier = ".intval($user->id)." WHERE id =".intval($sheet_id)." AND userid_creator=".intval($user->id);
      } else {
         $query = "INSERT INTO #__jobtest_sheet (title, category, totaltime, totalscore, questioncount, created, userid_creator, published, difficulty) VALUES (".$db->quote($post['title']).",0,".intval($post['totaltime']).",0,0,UTC_TIMESTAMP,".$user->id.",0,'low')";
      }
      $db->setQuery($query);

      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      } else {
         if (empty($sheet_id)) {
            $info['id'] = $db->insertid();
            $info['jobid'] = $job_id;
            $info['title'] = $post['title'];
            return $info;
         }

         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobtest_sheet-'.intval($sheet_id)));

         return true;
      }
   }

   public function saveQuestion() {
      if(!PSession::checkToken('post')) {
         $this->setError('非法的系统安全码');
         return false;
      }
      $post = JRequest::get('post', JREQUEST_ALLOWRAW);
      $db = PFactory::getDbo();

      $job_id = empty($post['job_id']) ? '0' : $post['job_id'];
      $sheet_id = empty($post['sheet_id']) ? '0' : $post['sheet_id'];
      $question_id = empty($post['question_id']) ? '0' : $post['question_id'];
      $user = PFactory::getMasterUser();
      
      if(!PCompanyHelper::checkJobManagerPermissions($job_id)) {
         $this->setError('您无权访问该资源');
         return false;
      }

      if (empty($sheet_id)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if (!empty($question_id)) { // Edit
         if (empty($post['question']) || empty($post['score'])) {
            $this->setError('请完成所有必填项！');
            return false;
         }
         if (empty($post['typeid'])) {
            $this->setError('网络异常，请重试！');
            return false;
         }
      } else { // Add
         if (empty($post['typeid']) || empty($post['question']) || empty($post['score'])) {
            $this->setError('请完成所有必填项！');
            return false;
         }
      }

      // Check if operator own this sheet
      if (!empty($question_id)) {
         // Allow edit
         $query = "SELECT COUNT(*) FROM #__jobtest_sheet WHERE id = ".intval($sheet_id)." AND userid_creator = ".intval($user->id);
      } else {
         // Check hits if add question
         $query = "SELECT COUNT(*) FROM #__jobtest_sheet WHERE hits = 0 AND id = ".intval($sheet_id)." AND userid_creator = ".intval($user->id);
      }
      $db->setQuery($query);
      if ($db->loadResult() <= 0) {
         $this->setError('这份试卷已有应聘者做过, 不可增删题目');
         return false;
      }

      // When edit question's score, check current sheet is hit or not.
      if (!empty($question_id)) {
         $query = "SELECT COUNT(*) FROM #__jobtest_sheet WHERE hits = 0 AND id = ".intval($sheet_id)." AND userid_creator = ".intval($user->id);
         $db->setQuery($query);
         $is_hit = $db->loadResult() <= 0;

         if ($is_hit) {
            // Check post score is equal to score in db
            $query = "SELECT score FROM #__jobtest_sheetquestions WHERE sheetid = ".intval($sheet_id)." AND questionid = ".intval($question_id);
            $db->setQuery($query);
            $db_score = $db->loadResult();

            if (($db_score != $post['score']) && !empty($post['score']) && !empty($db_score)) {
               $this->setError('该题已有应聘者做过, 不可修改题目分值(原分值'.$db_score.'分)');
               return false;
            }

            if (empty($post['score']))
               $post['score'] = $db_score;
         }
      }

      // Pre operations
      $answers = array();
      if($post['typeid'] == 1) { // Singleanswer
         if (!isset($post['correctanswer']) || $post['correctanswer'] == null) {
            $this->setError('请选择正确答案！');
            return false;
         }
         $correct = $post['correctanswer'];

         if (!isset($post['singleanswer'][$correct]) || $post['singleanswer'][$correct] == null) {
            $this->setError('正确答案的选项内容不能为空！');
            return false;
         }
         foreach ($post['singleanswer'] as $k => $v) {
            if ($v != null) {
               if ($k == $correct) {
                  $answers[] = array('choice'=>$v,'answer'=>1);
               } else {
                  $answers[] = array('choice'=>$v,'answer'=>0);
               }
            }
         }
         if (count($answers) < 2) {
            $this->setError('请至少填写两个选项！');
            return false;
         }
      } elseif ($post['typeid'] == 3) { // Freeinput
         $answers[] = htmlspecialchars($post['answer']);
      }

      // Update operation
      if (!empty($question_id)) {
         // Update jos_jobtest_question
         $query = "UPDATE #__jobtest_question SET question = ".$db->quote(htmlspecialchars(trim($post['question']))).",modified = UTC_TIMESTAMP, userid_modifier = ".intval($user->id)." WHERE id =".intval($question_id)." AND userid_creator = ".intval($user->id);
         $db->setQuery($query);
         if (!$db->execute()) {
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Generate question's img for onlinetesting use
         $config = array(
               'width' => '864',
               'color' => '#666666',
               'bg_color' => '#ffffff',
               'font_size' => '12',
               'row_spacing' => '4',
               'custom_name' => 'question_'.$question_id,
               'sub_folder' => $user->id
            );
         $img_url = text2img(trim($post['question']), $config);
         $question_img = $img_url ? $img_url : '';

         // Update jos_jobtest_question_details
         $query = "UPDATE #__jobtest_question_details SET question = ".$db->quote(htmlspecialchars(trim($post['question']))).",question_img = ".$db->quote($question_img).", answer = ".$db->quote(json_encode($answers))." WHERE questionid =".intval($question_id);
         $db->setQuery($query);
         if (!$db->execute()) {
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Update jos_jobtest_sheetquestions
         $query = "UPDATE #__jobtest_sheetquestions SET score = ".intval($post['score'])." WHERE questionid =".intval($question_id)." AND sheetid = ".intval($sheet_id);
         $db->setQuery($query);
         if (!$db->execute()) {
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Update sheet total questions and total score
         $query = "UPDATE #__jobtest_sheet SET totalscore = (SELECT SUM(score) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id)."),questioncount = (SELECT COUNT(*) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id).") WHERE id =".intval($sheet_id);
         $db->setQuery($query);
         if (!$db->execute()) {
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobtest_sheet-'.intval($sheet_id)));

      } else { // Insert operation
         // Insert jos_jobtest_question
         $query = "INSERT INTO #__jobtest_question (category, typeid, difficulty, question, created, userid_creator, public, published) VALUES (0, ".intval($post['typeid']).", 'low', ".$db->quote(htmlspecialchars(trim($post['question']))).", UTC_TIMESTAMP, ".intval($user->id).", 0, 1)";
         $db->setQuery($query);
         if (!$db->execute()) {
            $this->setError('网络异常，请重试！');
            return false;
         }
         $question_id = $db->insertid();

         // Generate question's img for onlinetesting use
         $config = array(
               'width' => '864',
               'color' => '#666666',
               'bg_color' => '#ffffff',
               'font_size' => '12',
               'row_spacing' => '4',
               'custom_name' => 'question_'.$question_id,
               'sub_folder' => $user->id
            );
         $img_url = text2img(trim($post['question']), $config);
         $question_img = $img_url ? $img_url : '';

         // Insert jos_jobtest_question_details
         $query = "INSERT INTO #__jobtest_question_details (questionid, question, question_img, answer) VALUES (".intval($question_id).", ".$db->quote(htmlspecialchars(trim($post['question']))).", ".$db->quote($question_img).", ".$db->quote(json_encode($answers)).")";
         $db->setQuery($query);
         if (!$db->execute()) {
            // Falling back to beginning
            $query = "DELETE FROM #__jobtest_question WHERE id=".intval($question_id);
            $db->setQuery($query);
            $db->execute();
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Insert jos_jobtest_sheetquestions
         $query = "INSERT INTO #__jobtest_sheetquestions (sheetid, questionid, score) VALUES (".intval($sheet_id).", ".intval($question_id).", ".intval($post['score']).")";
         $db->setQuery($query);
         if (!$db->execute()) {
            // Falling back to beginning
            $query = "DELETE FROM #__jobtest_question WHERE id=".intval($question_id);
            $db->setQuery($query);
            $db->execute();
            $query = "DELETE FROM #__jobtest_question_details WHERE question_id=".intval($question_id);
            $db->setQuery($query);
            $db->execute();
            $this->setError('网络异常，请重试！');
            return false;
         }

         // Update sheet total questions and total score
         $query = "UPDATE #__jobtest_sheet SET totalscore = (SELECT SUM(score) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id)."),questioncount = (SELECT COUNT(*) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id).") WHERE id =".intval($sheet_id);
         $db->setQuery($query);
         if (!$db->execute()) {
            // Falling back to beginning
            $query = "DELETE FROM #__jobtest_question WHERE id=".intval($question_id);
            $db->setQuery($query);
            $db->execute();
            $query = "DELETE FROM #__jobtest_question_details WHERE question_id=".intval($question_id);
            $db->setQuery($query);
            $db->execute();
            $this->setError('网络异常，请重试！');
            return false;
         }
      }

      return array('qid' => $question_id, 'job_id' => $job_id, 'sheet_id' => $sheet_id);
   }

   public function deleteQuestion() {
      $post = JRequest::get('post');
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      $qid = $post['id'];
      $job_id = $post['job_id'];
      $sheet_id = $post['sheet_id'];
      
      if(!PCompanyHelper::checkJobManagerPermissions($job_id)) {
         $this->setError('您无权访问该资源');
         return false;
      }

      // Check if operator own this sheet and this sheet haven't been seen
      $query = "SELECT COUNT(*) FROM #__jobtest_sheet WHERE hits = 0 AND id = ".intval($sheet_id)." AND userid_creator = ".intval($user->id);
      $db->setQuery($query);
      if ($db->loadResult() <= 0) {
         $this->setError('这份试卷已有应聘者做过, 不可增删题目');
         return false;
      }

      // Fix Me: Say we are working on "question database" idea in the future, we may have the situation that one question is linked to multiple sheets. So in here, we need all the sheet ids for later update sheet info use. (Consider of deadline, this version will not have this function)

      // Delete this question
      $query = "DELETE FROM #__jobtest_question WHERE id = ".intval($qid);
      $db->setQuery($query);
      $db->execute();
      $query = "DELETE FROM #__jobtest_question_details WHERE questionid = ".intval($qid);
      $db->setQuery($query);
      $db->execute();
      $query = "DELETE FROM #__jobtest_sheetquestions WHERE questionid = ".intval($qid);
      $db->setQuery($query);
      $db->execute();

      // Update this sheet's totalscore and questioncount
      $query = "UPDATE #__jobtest_sheet SET totalscore = (SELECT SUM(score) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id)."),questioncount = (SELECT COUNT(*) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheet_id).") WHERE id =".intval($sheet_id);
      $db->setQuery($query);

      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobtest_sheet-'.intval($sheet_id)));
      return true;
   }

   public function deleteSheet() {
      if(!PSession::checkToken('post')) {
         $this->setError('非法的系统安全码');
         return false;
      }
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      $jobid = JRequest::getInt('jobid', 0);
      $sheetid = JRequest::getInt('sheetid', 0);
      
      if(!PCompanyHelper::checkJobManagerPermissions($jobid)) {
         $this->setError('您无权访问该资源');
         return false;
      }

      // Check if operator own this sheet and this sheet has been seen or not
      $query = "SELECT COUNT(*) FROM #__jobtest_sheet WHERE hits = 0 AND id = ".intval($sheetid)." AND userid_creator = ".intval($user->id);
      $db->setQuery($query);
      if ($db->loadResult() <= 0) {
         $this->setError('这份试卷已有应聘者做过, 不可删除试卷');
         return false;
      }

      // Delete this sheet after check
      $query = "DELETE FROM #__jobtest_sheet WHERE id = ".intval($sheetid)." AND userid_creator = ".intval($user->id);
      $db->setQuery($query);

      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      $query = "DELETE FROM #__jobtest_sheetquestions WHERE sheetid = ".intval($sheetid);
      $db->setQuery($query);

      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobtest_sheet-'.intval($sheetid)));
      return true;
   }

   public function initQuestions($sheetid) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      $query = "SELECT jsq.questionid,jqd.question,jqd.answer,js.id AS sheetid,jqt.type,jsq.score, jq.typeid
         FROM #__jobtest_question_details jqd
         LEFT JOIN #__jobtest_sheetquestions jsq ON jqd.questionid = jsq.questionid
         LEFT JOIN #__jobtest_sheet js ON js.id = jsq.sheetid
         LEFT JOIN #__jobtest_question jq ON jsq.questionid = jq.id
         LEFT JOIN #__jobtest_questiontype jqt ON jqt.id = jq.typeid
         WHERE js.userid_creator=".intval($user->id)." and js.id = ".intval($sheetid)." ORDER BY jq.id DESC";
      $db->setQuery($query);
      $paper = $db->loadObjectList();

      $question_type = array('singleanswer'=>'客观题','freeinput'=>'主观题');

      foreach ($paper as $k => $ques) {
         if (isset($ques->answer)) {
            $ques->answer = json_decode($ques->answer);
         }
         $ques->typename = $question_type[$ques->type];
      }
      return $paper;
   }

   public function getQuestionDetail($question_id, $sheet_id) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      $query = "SELECT jsq.questionid,jqd.question,jqd.answer,js.id AS sheetid,jqt.type,jsq.score, jq.typeid, js.published, js.hits
         FROM #__jobtest_question_details jqd
         LEFT JOIN #__jobtest_sheetquestions jsq ON jqd.questionid = jsq.questionid
         LEFT JOIN #__jobtest_sheet js ON js.id = jsq.sheetid
         LEFT JOIN #__jobtest_question jq ON jsq.questionid = jq.id
         LEFT JOIN #__jobtest_questiontype jqt ON jqt.id = jq.typeid
         WHERE js.userid_creator=".intval($user->id)." and js.id = ".intval($sheet_id)." AND jq.id = ".intval($question_id);
      $db->setQuery($query);
      $question = $db->loadObject();

      $q_type = array('singleanswer'=>'客观题','freeinput'=>'主观题');

      if (isset($question->answer) && !empty($question->answer)) {
         $question->answer = json_decode($question->answer);
      }
      $question->typename = $q_type[$question->type];

      return $question;
   }
   
   public function getExamQuestions($sheetid, $start=0, $limit=5) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      
      $query = "SELECT jsq.questionid,jqd.question,jqd.question_img,jqd.answer,js.id AS sheetid, jqt.type
         FROM #__jobtest_question_details jqd
         LEFT JOIN #__jobtest_sheetquestions jsq ON jqd.questionid = jsq.questionid
         LEFT JOIN #__jobtest_sheet js ON js.id = jsq.sheetid
         LEFT JOIN #__jobtest_question jq ON jsq.questionid = jq.id
         LEFT JOIN #__jobtest_questiontype jqt ON jqt.id = jq.typeid
         WHERE js.userid_creator=".intval($user->id)." and js.id = ".intval($sheetid)." ORDER BY jq.typeid";
      $db->setQuery($query,$start,$limit);
      $paper = $db->loadObjectList();
      $paper['last_end'] = $start+$limit;

      foreach ($paper as $k => $ques) {
         if (isset($ques->answer)) {
            $ques->answer = json_decode($ques->answer);
         }
      }
      return $paper;
   }

   public function showJobdetails($jobid) {
      $db = PFactory::getDbo();

      $query =  'SELECT j.*, js.specialization, jje.exp_name,jje.min_years,jst.salary_type,jst.max_salary, pj.sheetid as has_persontest, 
                jjp.position,jjp2.position as position2, u.name as companyname, IF(jff.folder_id IS NULL, 0, 1) AS is_saved,
                jzj.city AS zhilian_city,jzj.id_city AS zhilian_id_city,jlj.positionAddress AS positionaddress,jlj.positionAdvantage AS positionadvantage,jlj.salary_max AS salarymax,jlj.salary_min AS salarymin,jlj.lagou_job_id '.
                ' from #__jobs_job as j '.
                ' left join #__jobs_job_spec as js on j.id_job_spec_categ=js.id '.
                ' left join #__jobs_job_exp as jje on jje.id=j.id_job_exp '.
                ' left join #__jobs_salary_type as jst on jst.id=j.id_salary_type '.
                ' left join #__persontest_jobsheet as pj on pj.jobid=j.id '.
                ' left join #__jobs_job_position as jjp on jjp.id=j.id_pos_type '.
                ' left join #__jobs_job_position as jjp2 on jjp2.id=j.id_pos_type2 '.
                ' left join #__users as u on u.id=j.employer_id '.
                ' left join #__jobs_zhilian_job AS jzj ON jzj.job_id = j.id '.
                ' left join #__jobs_lagou_job AS jlj ON jlj.job_id = j.id '.
                " LEFT JOIN *PREFIX*hr_job_folder AS jf ON (jf.folder_name='我的收藏' AND jf.owner=" . $db->quote(OC_User::getUser()) . " AND jf.system=1)
                  LEFT JOIN *PREFIX*hr_job_folder_files AS jff ON (jff.folder_id=jf.folder_id AND jff.job_id=j.id) " .
                ' where j.id=' . intval($jobid) . 
                ' group by j.id';

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_jobpos');
      // get cached data now 
      $data = $cache->call(array($this, '_showJobdetails'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      if ( $data && CacheHelper::needTriggers($cache) ) {
         $tables = array('#__jobs_job-'.intval($jobid), '*PREFIX*hr_company_interview-'.intval($jobid), '#__jobtest_jobsheet-'.intval($jobid));
         // we use company id, so changes will be triggered for all company users 
         CacheHelper::setTriggers($data->employer_id, $cache, $tables);
      }

      return $data; 
   }

   public function _showJobdetails($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   public function chooseJobsheet($jobid, $sheetid){
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      if($jobid == 0){
         $this->setError('非法职位');
         return false;
      }
      if(!PCompanyHelper::checkJobManagerPermissions($jobid)) {
         $this->setError('您无权访问该资源');
         return false;
      }
      
      $db = PFactory::getDbo();

      // Check this new sheet have any questions
      $query = "SELECT COUNT(*) FROM #__jobtest_sheetquestions WHERE sheetid=".intval($sheetid);
      $db->setQuery($query);
      $q_num = $db->loadResult();
      if ($q_num <= 0) {
         $this->setError('没有试题的试卷不能被选定哦');
         return false;
      }

      // update jobsheet sheetid
      $query = 'delete from #__jobtest_jobsheet where jobid='.intval($jobid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      $query = 'insert into  #__jobtest_jobsheet (jobid, sheetid) values ('.intval($jobid).', '.intval($sheetid).')';
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      // Set this sheet published
      $query = "UPDATE #__jobtest_sheet SET published = 1 WHERE id = ".intval($sheetid);
      $db->setQuery($query);
      $db->execute();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobs_job', '#__jobs_job-'.intval($jobid), '#__jobtest_sheet', '#__jobtest_sheet-'.intval($sheetid)));

      // stay on master: read-your-write 
      $db->stayOnMaster(); 
      // update online_interview_requirement status
      JobHelper::setJobRequirements($jobid, 'onlinetest');

      // leave master enforcement now
      $db->leaveOnMaster();

      return true;
   }
   
   public function cancelJobsheet($jobid){
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      
      if(!PCompanyHelper::checkJobManagerPermissions($jobid)) {
         $this->setError('您无权访问该资源');
         return false;
      }

      // update jobsheet sheetid
      $db = PFactory::getDbo();
      $query = 'delete from #__jobtest_jobsheet where jobid='.intval($jobid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
         
      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobs_job', '#__jobs_job-'.intval($jobid)));

      // stay on master: read-your-write 
      $db->stayOnMaster(); 
      // update online_interview_requirement status
      JobHelper::setJobRequirements($jobid, 'onlinetest');
   
      // leave on master enforcement now
      $db->leaveOnMaster(); 

      return true;
   }
   
   public function showJobsheetDetails($jobid){
         $db = PFactory::getDbo();
         
         $query = 'select jj.jobid, jj.sheetid, js.title ' . 
                  ' from #__jobtest_jobsheet as jj ' . 
                  ' left join #__jobtest_sheet as js on jj.sheetid=js.id ' . 
                  ' where jj.jobid='.intval($jobid);
                  
         $db->setQuery(PUtil::replaceOCPrefix($query));
         return $db->loadObject();
   }
   
   public function showPersontestsheetDetails($jobid){
      $db = PFactory::getDbo();
      
      $query = 'select pj.jobid, pj.sheetid, ps.title ' . 
               ' from #__persontest_jobsheet as pj ' . 
               ' left join #__persontest_sheet as ps on pj.sheetid=ps.id ' . 
               ' where pj.jobid='.intval($jobid);
               
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }
   
   public function showSheets(){
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      
      $query = 'select js.title, js.id as sheetid, js.userid_creator AS owner, js.published, js.hits, '.
                       '(select group_concat(jobid) from #__jobtest_jobsheet where sheetid=js.id) as selected_by_jobs '.
                       'from #__jobtest_sheet as js '.
                       'where js.userid_creator='.intval($user->id);
               
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList();
   }
   
   /* upload sheet */
   public function uploadSheet($jobid) {
      if(!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }

         $user = PFactory::getMasterUser();
         $app = PFactory::getApplication();
         $post   = JRequest::get('post');
         $upload =array(
                   'tmp_name' => null,
                   'name' => null,
                   'size' => null,
                   'type' => null,
                   'error' => ""
         );

         if(empty($_FILES['Filedata'])) {
            return jsonError("没有上传数据", null, false);
         }

         $upload = array_merge($upload, $_FILES['Filedata']);

         // Check for security token 
         if(!PSession::checkToken()) {
            $upload['error'] = '登录已失效，请重新登录';
            return json_encode($upload);
         }

         if($user->get("guest") || $user->get("id") <= 0) {
            $upload['error'] = '请先登录再上传考题';
            return json_encode($upload);
         }

         //UPLOAD FILE
         $allowTypes = array("application/msword",
                        "application/kswps",
                        "application/vnd.ms-office",
                        "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                        "application/pdf",
                        "application/vnd.oasis.opendocument.text",
                        "application/vnd.oasis.opendocument.spreadsheet",
                        "image/jpeg",
                        "image/png",
                        );

         $target_path = JPath::clean(COMPANY_JOBTEST_BASEDIR.'/'.$jobid);
         if(!JFolder::exists($target_path))
             JFolder::create($target_path);

         $filedata = $_FILES['Filedata'];
         $fileext = JFile::getExt($filedata['name']);
         $filedata['name'] = JApplication::stringURLSafe(JFile::stripExt($filedata['name'])) . "." . $fileext;
         $filedata['name'] = str_replace(' ', '_', trim(strtolower($filedata['name'])));
         $filedata['name'] = $jobid. "." . $fileext;

         $handle = new Upload($filedata, 'zh_CN');
         $handle->file_auto_rename = false;
         $handle->file_overwrite = true;
         if(!$handle) {
            $upload['error'] = $handle->error;
            return json_encode($upload);
         }

         $handle->allowed = $allowTypes;
         $handle->process($target_path);
         if (!$handle->processed) {
            $upload['error'] = $handle->error;
            return json_encode($upload);
         }
         
         //send email 
         require_once JPATH_SITE.'/components/com_jobmail/models/email.php';
         $attachment = $model = new JobmailModelEmail(); 
         $attachment = $target_path.'/'.$filedata['name']; 
         $config = PFactory::getConfig(); 
         $receiver = (isDevMode() || isTestMode()) ? $config->get('employeremail', '') : 'support@pipapai.com'; 
         $supportmail = $config->get('mailfrom','');

         // $mailFrom 	= $app->getCfg('mailfrom');
         // $fromName 	= $app->getCfg('fromname');
         $model->setVariables(array('recipient'=>$receiver,
                                    'mailFrom'=>$supportmail, 
                                    'fromName'=>$user->name,
                                    'attachment'=>$attachment,
                                    'subject'=>'在线笔试',
                                    'body'=>'请为公司<b>'.$user->name.'</b>添加在线笔试套题，套题见附件。'));
         $model->send(1, true);
         
         // update sheet upload status;
         $db = PFactory::getDbo();
         $now = JFactory::getDate()->toSql();
         $query = 'insert into  #__jobs_jobupload (employer_id, jobid, job_file, upload_datetime) values ('.intval($user->id).', '.intval($jobid).', '.$db->quote($attachment).', '.$db->quote($now).')';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
         
         //update jobsheet status;
         $query = 'delete from #__jobtest_jobsheet where jobid='.intval($jobid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
         JobHelper::setJobRequirements($jobid, 'onlinetest');
         
         return json_encode($upload);
   }
   
   /* set job auto_invite */
   public function setAutoInvite($jobid, $auto) {
      $db = PFactory::getDbo();
      $query = 'UPDATE #__jobs_job SET auto_invite=' . intval($auto) . ' where id='.intval($jobid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError('自动邀请应聘者设置失败！');
         return false;
      } 

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('#__jobs_job-'.intval($jobid)));

      return true;
   }

   public function saveJobPosition(){
      // simple spoof check security
      if(!PSession::checkToken()) {
          $this->setError('登录已失效，请重新登录');
          return false;
      }

      $post   = JRequest::get('post', JREQUEST_ALLOWRAW);
      $db = PFactory::getDbo();
      $db->stayOnMaster(); 
      if(empty($post['job_id'])) {
         if(!PCompanyHelper::checkCRUDPermission()) {
            $db->leaveOnMaster(); 
            $this->setError('您无权访问该资源');
            return false;
         }     
      } else {
         if(!PCompanyHelper::checkJobManagerPermissions($post['job_id'])) {
            $db->leaveOnMaster(); 
            $this->setError('您无权访问该资源');
            return false;
         }
      }
      //add jobposition
      if(empty($post['job_id'])){
         if(empty($post['job_title']) || empty($post['location_ids']) || empty($post['id_pos_type']) || empty($post['long_desc'])){
            $db->leaveOnMaster();
            $this->setError('网络异常，请重试！');
            return false;
         }
      }else{
         if(empty($post['job_title']) || empty($post['id_pos_type']) || empty($post['long_desc'])){
            $db->leaveOnMaster();
            $this->setError('网络异常，请重试！');
            return false;
         }
      }

      $user = PFactory::getMasterUser(); 
      $location_ids = explode(",", $post['location_ids']);
      JArrayHelper::toInteger($location_ids);
      //subaccount check the new job if have permission this job
      if(empty($post['job_id']) && !empty($location_ids) && !PFactory::isMasterUser() && PCompanyHelper::isJobManager()){
         if(($existed_cities =$this->checkSubaccountPostJobLocation($post['job_title'],$location_ids))!=null) {
            $db->leaveOnMaster();
            $this->setError(implode(",", $existed_cities).'的职位已存在!');
            return false;
         }
      }
               
      if(empty($post['job_id']) || (!empty($post['job_id']) && !empty($post['location_ids']))){
         $query = 'SELECT p.provinceid, p.province, c.cityid, c.city FROM #__provincelist as p '.
               'left join #__citylist as c on p.provinceid=c.provinceid '.
               'where c.cityid in ('.implode(",", $location_ids).')';
         $db->setQuery($query);
         $cities = $db->loadAssocList();
         if(empty($cities)){
            $db->leaveOnMaster();
            $this->setError('网络异常，请重试！');
            return false;
         }         
      }elseif(!empty($post['job_id']) && empty($post['location_ids'])){
         $jobpos_id_state = $post['jobpos_id_state'];
         $query = 'SELECT provinceid,province FROM #__provincelist WHERE provinceid='.intval($jobpos_id_state);
         $db->setQuery($query);
         $province_info = $db->loadAssoc();
         if(empty($province_info)){
            $db->leaveOnMaster();
            $this->setError('网络异常，请重试！');
            return false;
         }
      }
      // xss 
      $long_desc = clear_xss($post['long_desc']);

      // Get syncing sources
      $syncs = JRequest::getVar("syncto", array(), 'POST', 'array');
      $syncs = array_filter($syncs);
      $syncing_sources = empty($syncs) ? null : json_encode($syncs);

      # group id is used to group all same job positions which only differ from location - easy for editing/sync/interview setup 
      $groupid = 0;
      $positions = array();
      array_push($positions, empty($post['id_pos_type_text']) ? '' : $post['id_pos_type_text']);
      array_push($positions, empty($post['zhilian_id_pos_type_text']) ? '' : $post['zhilian_id_pos_type_text']);
      array_push($positions, empty($post['jiancai_id_pos_type_text']) ? '' : $post['jiancai_id_pos_type_text']);
      array_push($positions, empty($post['lagou_id_pos_type_text']) ? '' : $post['lagou_id_pos_type_text']);
      array_push($positions, empty($post['id_pos_type2_text']) ? '' : $post['id_pos_type2_text']);
      array_push($positions, empty($post['zhilian_id_pos_type2_text']) ? '' : $post['zhilian_id_pos_type2_text']);
      array_push($positions, empty($post['jiancai_id_pos_type2_text']) ? '' : $post['jiancai_id_pos_type2_text']);
      $pos_txt = PJobpositionHelper::getPosTxt($positions);
      $jobids = array();
      if(empty($post['job_id']) || (!empty($post['job_id'])) && !empty($post['location_ids'])){
         foreach ($cities as $c) {
            $sql = '('.intval($groupid).',
                   '.$db->quote($post['job_title']).',
                   '.$user->id.',
                   "pipapai",
                   '.$db->quote($pos_txt).',
                   '.intval($post['id_degree_level']).',
                   '.intval($post['id_pos_type']).',
                   '.intval($post['id_pos_type2']).',
                   '.intval($post['id_salary_type']).',
                   '.intval($post['id_job_exp']).',
                   '.intval($post['id_fl1']).',
                   '.intval($post['id_fl_level1']).',
                   '.intval($post['id_fl2']).',
                   '.intval($post['id_fl_level2']).',
                   '.intval($post['agefrom']).',
                   '.intval($post['ageto']).',
                   '.$db->quote($post['department']).',
                   '.$db->quote($post['keywords']).',
                   '.$db->quote($post['job_number']).',
                   '.intval($post['employment_type']).',
                   '.$db->quote($c['province']).',
                   '.intval($c['provinceid']).',
                   '.$db->quote($c['city']).',
                   '.intval($c['cityid']).',
                   '.intval($post['num_needed']).',
                   '.$db->quote($long_desc).',
                   UTC_TIMESTAMP(),
                   '.$db->quote($syncing_sources).' )';
         
            $query = 'INSERT INTO #__jobs_job (group_id, job_title,employer_id, source, pos_txt, id_degree_level,id_pos_type,id_pos_type2,id_salary_type,id_job_exp,id_fl1,id_fl_level1,
                    id_fl2,id_fl_level2,agefrom,ageto,department,keywords,job_number,employment_type,state,id_state,city,id_city,num_needed,long_desc,publish_date,syncing_sources)
                    values '.$sql.' ON DUPLICATE KEY UPDATE pos_txt=values(pos_txt),
                    id_degree_level=values(id_degree_level),
                    id_pos_type=values(id_pos_type),
                    id_pos_type2=values(id_pos_type2),
                    id_salary_type=values(id_salary_type),
                    id_job_exp=values(id_job_exp),
                    id_fl1=values(id_fl1),
                    id_fl_level1=values(id_fl_level1),
                    id_fl2=values(id_fl2),
                    id_fl_level2=values(id_fl_level2),
                    agefrom=values(agefrom),
                    ageto=values(ageto),
                    department=values(department),
                    keywords=values(keywords),
                    job_number=values(job_number),
                    employment_type=values(employment_type),
                    num_needed=values(num_needed),
                    long_desc=values(long_desc),
                    publish_date=values(publish_date),
                    syncing_sources=values(syncing_sources),
                    id=LAST_INSERT_ID(id)';
             
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if(!$db->execute()) {
               $db->leaveOnMaster();
               $this->setError('网络异常，请重试！');
               return false;
            }
            // assign subaccount jobmanager
            $query = 'SELECT LAST_INSERT_ID()';
            $db->setQuery($db->useLastConnection($query));
            $jobid = $db->loadResult();
            $groupid = empty($groupid) ? $jobid : $groupid;
            if (!empty($jobid) && (empty($post['job_id']) || $jobid != $post['job_id'])) {
               array_push($jobids, $jobid);
            }           
         }
      }elseif((!empty($post['job_id'])) && empty($post['location_ids'])){
         $sql = '('.intval($groupid).',
                   '.$db->quote($post['job_title']).',
                   '.$user->id.',
                   "pipapai",
                   '.$db->quote($pos_txt).',
                   '.intval($post['id_degree_level']).',
                   '.intval($post['id_pos_type']).',
                   '.intval($post['id_pos_type2']).',
                   '.intval($post['id_salary_type']).',
                   '.intval($post['id_job_exp']).',
                   '.intval($post['id_fl1']).',
                   '.intval($post['id_fl_level1']).',
                   '.intval($post['id_fl2']).',
                   '.intval($post['id_fl_level2']).',
                   '.intval($post['agefrom']).',
                   '.intval($post['ageto']).',
                   '.$db->quote($post['department']).',
                   '.$db->quote($post['keywords']).',
                   '.$db->quote($post['job_number']).',
                   '.intval($post['employment_type']).',
                   '.$db->quote($province_info['province']).',
                   '.intval($province_info['provinceid']).',
                   '.intval($post['num_needed']).',
                   '.$db->quote($long_desc).',
                   UTC_TIMESTAMP(),
                   '.$db->quote($syncing_sources).' )';        
         $query = 'INSERT INTO #__jobs_job (group_id, job_title,employer_id, source, pos_txt, id_degree_level,id_pos_type,id_pos_type2,id_salary_type,id_job_exp,id_fl1,id_fl_level1,
                    id_fl2,id_fl_level2,agefrom,ageto,department,keywords,job_number,employment_type,state,id_state,num_needed,long_desc,publish_date,syncing_sources)
                    values '.$sql.' ON DUPLICATE KEY UPDATE pos_txt=values(pos_txt),
                    id_degree_level=values(id_degree_level),
                    id_pos_type=values(id_pos_type),
                    id_pos_type2=values(id_pos_type2),
                    id_salary_type=values(id_salary_type),
                    id_job_exp=values(id_job_exp),
                    id_fl1=values(id_fl1),
                    id_fl_level1=values(id_fl_level1),
                    id_fl2=values(id_fl2),
                    id_fl_level2=values(id_fl_level2),
                    agefrom=values(agefrom),
                    ageto=values(ageto),
                    department=values(department),
                    keywords=values(keywords),
                    job_number=values(job_number),
                    employment_type=values(employment_type),
                    num_needed=values(num_needed),
                    long_desc=values(long_desc),
                    publish_date=values(publish_date),
                    syncing_sources=values(syncing_sources),
                    id=LAST_INSERT_ID(id)';
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $db->leaveOnMaster();
            $this->setError('网络异常，请重试！');
            return false;
         }
         // assign subaccount jobmanager
         $query = 'SELECT LAST_INSERT_ID()';
         $db->setQuery($db->useLastConnection($query));
         $jobid = $db->loadResult();
         $groupid = empty($groupid) ? $jobid : $groupid;
         if (!empty($jobid) && (empty($post['job_id']) || $jobid != $post['job_id'])) {
            array_push($jobids, $jobid);
         }
      }
      // We need to INSERT/UPDATE jos_jobs_zhilian_job's id_pos_type1 and id_pos_type2, WHEN add or edit jobposition(s)
      $zhilian_id_pos_type = JRequest::getInt('zhilian_id_pos_type', 0);
      $zhilian_id_pos_type2 = JRequest::getInt('zhilian_id_pos_type2', 0);
      $zhilian_id_city = JRequest::getInt('zhilian_id_city',0);
      $zhilian_city = JRequest::getString('zhilian_city_text',null);

      if ( in_array('zhilian', $syncs) || !empty($zhilian_id_pos_type) || !empty($zhilian_id_pos_type2) ) {        
         if(!empty($post['job_id']) && empty($post['location_ids']) && empty($zhilian_id_city)){
            $db->leaveOnMaster();
            $this->setError('请选择对应工作地点!');
            return false;
         }      
         if (empty($zhilian_id_pos_type) && empty($zhilian_id_pos_type2)) {
            $db->leaveOnMaster(); 
            $this->setError('请填写对应职能类别!');
            return false;
         }
         $sql = "";
         if (!empty($post['job_id'])) {
             $sql = "(".$db->quote($post['job_title']).", ".intval($post['job_id']).",".intval($zhilian_id_pos_type).", ".intval($zhilian_id_pos_type2).",".$db->quote($post['jobpos_state']).",".intval($post['jobpos_id_state']).",".$db->quote($zhilian_city).",".intval($zhilian_id_city).")";
         } else {
            // Also add some info into jobs_zhilian_job table when adding new job
            $values = array();
            foreach ($jobids as $k => $jobid) {
               array_push($values, "(".$db->quote($post['job_title']).", ".intval($jobid).",".intval($zhilian_id_pos_type).", ".intval($zhilian_id_pos_type2).",'',0,".$db->quote($zhilian_city).",".intval($zhilian_id_city).")");
            }
            $sql = implode(',', $values);
         }
         if (!empty($sql)) {
            $query = "INSERT INTO #__jobs_zhilian_job (job_title, job_id, id_pos_type1, id_pos_type2,state,id_state,city,id_city)
                  VALUES ".$sql."
                  ON DUPLICATE KEY UPDATE 
                  id_pos_type1=values(id_pos_type1), 
                  id_pos_type2=values(id_pos_type2),
                  state=values(state),
                  id_state=values(id_state),
                  city=values(city),
                  id_city=values(id_city)";
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if(!$db->execute()) {
               $db->leaveOnMaster(); 
               $this->setError('网络异常，请重试！');
               return false;
            }
         }
      }
      
      //INSERT/UPDATE jos_jobs_jiancai_job's id_pos_type1 and id_pos_type2, WHEN add or edit jobposition(s)
      $jiancai_id_pos_type = JRequest::getInt('jiancai_id_pos_type', 0);
      $jiancai_id_pos_type2 = JRequest::getInt('jiancai_id_pos_type2', 0);
      if ( in_array('jiancai', $syncs) || !empty($jiancai_id_pos_type) || !empty($jiancai_id_pos_type2) ) {
         if (empty($jiancai_id_pos_type) && empty($jiancai_id_pos_type2)) {
            $db->leaveOnMaster();
            $this->setError('请填写对应职能类别!');
            return false;
         }
         $sql = "";
         if (!empty($post['job_id'])) {
            $sql = "(".$db->quote($post['job_title']).", ".intval($post['job_id']).", ".intval($jiancai_id_pos_type).", ".intval($jiancai_id_pos_type2).")";
         } else {
            // Also add some info into jobs_jiancai_job table when adding new job
            $values = array();
            foreach ($jobids as $k => $jobid) {
               array_push($values, "(".$db->quote($post['job_title']).", ".intval($jobid).", ".intval($jiancai_id_pos_type).", ".intval($jiancai_id_pos_type2).")");
            }
            $sql = implode(',', $values);
         }
      
         if (!empty($sql)) {
            $query = "INSERT INTO #__jobs_jiancai_job (job_title, job_id, id_pos_type1, id_pos_type2)
                  VALUES ".$sql."
                  ON DUPLICATE KEY UPDATE id_pos_type1=values(id_pos_type1), id_pos_type2=values(id_pos_type2)";
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if(!$db->execute()) {
               $db->leaveOnMaster();
               $this->setError('网络异常，请重试！');
               return false;
            }
         }
      }

      //INSERT/UPDATE jos_jobs_lagou_job WHEN add or edit jobposition(s)
      $lagou_id_pos_type = JRequest::getInt('lagou_id_pos_type', 0);
      $salarymin = JRequest::getInt('salarymin', 0);
      $salarymax = JRequest::getInt('salarymax', 0);
      $lagou_employment_type = JRequest::getInt('employment_type', 0);
      $lagou_id_job_exp = JRequest::getInt('id_job_exp', 0);
      $lagou_employment_type = $lagou_employment_type==100 ? 0 : $lagou_employment_type;

      if ( in_array('lagou', $syncs)) {
         if (empty($lagou_id_pos_type)) {
            $db->leaveOnMaster();
            $this->setError('请填写对应职能类别!');
            return false;
         }
         if (empty($salarymin)) {
            $db->leaveOnMaster();
            $this->setError('请填写对应拉勾最低月薪!');
            return false;
         }
         if (empty($salarymax)) {
            $db->leaveOnMaster();
            $this->setError('请填写对应拉勾最高月薪!');
            return false;
         }
         if (empty($post['lagou_positionadvantage'])) {
            $db->leaveOnMaster();
            $this->setError('请填写对应拉勾职位诱惑!');
            return false;
         }
         preg_match_all("/./us", $post['lagou_positionadvantage'], $match);
         if (count($match[0])>20) {
            $db->leaveOnMaster();
            $this->setError('职位诱惑限20字内!');
            return false;
         }
         if ($salarymin > 100 || $salarymin < 1 || $salarymax > 100 || $salarymax < 1) {
            $db->leaveOnMaster();
            $this->setError('请输入有效的月薪范围，1k-100k之间!');
            return false;
         }
         if ($salarymin >= $salarymax) {
            $db->leaveOnMaster();
            $this->setError('最高月薪需大于最低月薪!');
            return false;
         }
         if ($salarymin < $salarymax/2) {
            $db->leaveOnMaster();
            $this->setError('最高月薪不能大于最低月薪的2倍!');
            return false;
         }
         $sql = "";
         $tsql = 'select * from *PREFIX*hr_thirdtools_company_mapping where company_id = '.intval($user->id).' and source = "lagou"';
         $db->setQuery(PUtil::replaceOCPrefix($tsql));
         $lagou_info = $db->loadAssoc();
         $lagou_company_id = empty($lagou_info['third_company_id'])?0:$lagou_info['third_company_id'];
         if (!empty($post['job_id'])) {
            $sql = "(".$db->quote($post['job_title']).", ".intval($post['job_id']).", ".intval($post['lagou_job_id']).", ".intval($lagou_id_pos_type).", ".intval($lagou_company_id).", ".$db->quote($post['salarymin']).", ".$db->quote($post['salarymax']).", ".$db->quote($post['lagou_positionadvantage']).", ".$db->quote($post['lagou_positionaddress']).", ".$db->quote($lagou_employment_type).", ".$db->quote($lagou_id_job_exp).")";
         } else {
            // Also add some info into jobs_lagou_job table when adding new job
            $values = array();
            foreach ($jobids as $k => $jobid) {
               array_push($values, "(".$db->quote($post['job_title']).", ".intval($jobid).", ".intval($post['lagou_job_id']).", ".intval($lagou_id_pos_type).", ".intval($lagou_company_id).", ".$db->quote($post['salarymin']).", ".$db->quote($post['salarymax']).", ".$db->quote($post['lagou_positionadvantage']).", ".$db->quote($post['lagou_positionaddress']).", ".$db->quote($lagou_employment_type).", ".$db->quote($lagou_id_job_exp).")");
            }
            $sql = implode(',', $values);
         }
      
         if (!empty($sql)) {
            $query = "INSERT INTO #__jobs_lagou_job (job_title, job_id, lagou_job_id, id_pos_type1, lagou_company_id, salary_min, salary_max, positionAdvantage, positionAddress, employment_type, id_job_exp)
                  VALUES ".$sql."
                  ON DUPLICATE KEY UPDATE id_pos_type1=values(id_pos_type1), id_pos_type2=values(id_pos_type2), salary_min=values(salary_min), salary_max=values(salary_max), positionAdvantage=values(positionAdvantage), positionAddress=values(positionAddress), employment_type=values(employment_type), id_job_exp=values(id_job_exp)";
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if(!$db->execute()) {
               $db->leaveOnMaster();
               $this->setError('网络异常，请重试！');
               return false;
            }
         }
      }

      if(!empty($jobids)) {
         $values = array();
         $subUser = PCompanyHelper::getSubUser();
         $adminAllUsers = PCompanyHelper::getJobAdminAllUser();

         if (!empty($adminAllUsers)) {
            foreach ($adminAllUsers as $adminAllUser) {
               foreach($jobids as $jobid) {
                  $values[] = "(" . intval($jobid) . ", " . $db->quote($adminAllUser->uid) . ")";
               }
            }
         }

         if (!empty($subUser->uid)) {
            //FIXME: don't call model in another model
            $subModel = new PCompanyModelSubAccounts();
            $permissions = $subModel->getUserPermissions($subUser->uid);
                 
            if(!($permissions & PERMISSION_JOB_ADMIN_ALL)) {
               foreach($jobids as $jobid) {
                  $values[] = "(" . intval($jobid) . ", " . $db->quote($subUser->uid) . ")";
               }
            }
            // if subuser doesn't have assiged permission, just give it
            $permChanged = false; 
            if(!($permissions & PERMISSION_JOB_ADMIN_ALL || $permissions & PERMISSION_JOB_ADMIN_ASSIGNED)) {
               $permissions |= PERMISSION_JOB_ADMIN_ASSIGNED;
               if(!$subModel->setUserPermissions($subUser->uid, $permissions)) {
                  $db->leaveOnMaster(); 
                  $this->setError('系统错误');
                  return false;
               }
               $permChanged = true; 
            }

            if($permChanged) {
               // Event Driven Cache/Object Caching - clear any cache related to affected tables 
               // We need to clear out cache for getSubUser
               CacheHelper::triggerRemove($subUser->uid, array('*PREFIX*hr_jobmanager'));
            }
         }

         if(!empty($values)) {
            $query = "INSERT IGNORE INTO *PREFIX*hr_jobmanager (jobid, manager) VALUES " . implode(",", $values);
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if (!$db->execute()) {
               $db->leaveOnMaster(); 
               $this->setError('系统错误');
               return false;
            }

            // Event Driven Cache/Object Caching - clear any cache related to affected tables 
            CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_jobmanager'));
            CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobmanager')); # alter all other subaccount's cache 
         }
      }
      

      if (empty($post['job_id']) && empty($groupid)) {
         $db->leaveOnMaster(); 
         $this->setError('发布职位失败：该职位已存在！');
         return false;
      }

      if(!empty($post['job_id'])) {
         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         CacheHelper::triggerRemove($user->id, array('#__jobs_job', '#__jobs_job-'.intval($post['job_id'])));
      }
      else {
         // Event Driven Cache/Object Caching - clear any cache related to affected tables 
         $tables = array('#__jobs_job');
         if (!empty($jobids)) {
            foreach($jobids as $jobid) {
               array_push($tables, '#__jobs_job-'.intval($jobid));
            }
         }
         CacheHelper::triggerRemove($user->id, $tables);
      }

      // After successfully update/add, we need to check sync information
      if(!empty($syncs)) {
         $session = PFactory::getSession(); 
         $session->set('jobpos_syncto', $syncs);
         $session->set('jobpos_addjob', empty($post['job_id']) ? 1 : 0);
      }

      $db->leaveOnMaster(); 
      $this->set('jobid', $groupid);
      return true;
   }

   /**
    * Get all the third part position id and text based on master base pos_id || jobid
    * @param  int $job_id pipapai base jobid
    * @param  int $pos_id pipapai base posid
    * @param  array $sources
    * @return array
    */
   public function getPosDetail($job_id=0, $pos_id=0, $sources) {
      if ( (empty($pos_id) || empty($sources)) && (empty($job_id) || empty($sources)) ) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      $db = PFactory::getDbo();
      $select_arr = array();
      $join_text = "";
      $query = "";

      $sources = array_filter(array_unique($sources));
      
      if (!empty($job_id)) {
         foreach ($sources as $k => $s) {
            if ($s != '51job') {
               array_push($select_arr, $s."p1.id AS ".$s."_id1, ".$s."p1.position AS ".$s."_text1, ".$s."p2.id AS ".$s."_id2, ".$s."p2.position AS ".$s."_text2,".$s."_jobs_job.id_city AS ".$s."_id_city, ".$s."_jobs_job.city AS ".$s."_city");
               $join_text .=" LEFT JOIN #__jobs_".$s."_job ".$s."j ON ".$s."j.job_id = jj.id
                              LEFT JOIN #__jobs_".$s."_job_position ".$s."p1 ON ".$s."p1.id=".$s."j.id_pos_type1
                              LEFT JOIN #__jobs_".$s."_job_position ".$s."p2 ON ".$s."p2.id=".$s."j.id_pos_type2
                              LEFT JOIN #__jobs_".$s."_job ".$s."_jobs_job ON ".$s."_jobs_job.job_id=jj.id";
            }
         }
         if (empty($select_arr) || empty($join_text))
            return false;
         $query = "SELECT ".implode(',', $select_arr).",jj.id_city FROM #__jobs_job jj ".$join_text." WHERE jj.id = ".intval($job_id);
      } elseif (!empty($pos_id)) {
         foreach ($sources as $k => $s) {
            if ($s != '51job') {
               array_push($select_arr, $s."p.id AS ".$s."_id, ".$s."p.position AS ".$s."_text ");
               $join_text .=" LEFT JOIN #__jobs_".$s."_job_position ".$s."p ON ".$s."p.".$s."_code = jjp.".$s."_code ";
            }
         }
         if (empty($select_arr) || empty($join_text))
            return false;

         $query = "SELECT ".implode(',', $select_arr)." FROM #__jobs_job_position jjp ".$join_text." WHERE jjp.id = ".intval($pos_id);
      }

      if (empty($query))
         return false;

      $db->setQuery(PUtil::replaceOCPrefix($query));
      $pos_detail = $db->loadAssoc();

      return $pos_detail;
   }
   
   public function checkPermissions($jobids = array(), $manager = true) {
      $isMasterUser = PFactory::isMasterUser();
      $checkManager = $isMasterUser ? false : $manager;
      
      $db = PFactory::getDbo();
      $query = 'SELECT jj.id FROM #__jobs_job AS jj ' .
               ($checkManager ? 'LEFT JOIN *PREFIX*hr_jobmanager AS jm ON (jm.jobid=jj.id AND jm.manager=' . $db->quote(OC_User::getUser()) . ')' : '') .
               'WHERE jj.employer_id=' . intval(PFactory::getMasterUser()->id) . ' AND jj.id IN (' . implode(',', $jobids) . ')' . ($checkManager ? ' AND jm.jobid IS NOT NULL' : '');
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('', 'id');
   }
   
   public function save($save=true) {
      $jobids = json_decode(JRequest::getString("ids", ""));
      if(empty($jobids))
         return true;
   
      JArrayHelper::toInteger($jobids);
      // check job owner
      $jobids = self::checkPermissions($jobids, false);
      if(empty($jobids)) {
         $this->setError("您无权访问该资源");
         return false;
      }
      $user=PFactory::getMasterUser();
      $db = PFactory::getDbo();
   
      $values = array();
      $folder_id = PJobpositionHelper::getSavedFolderID();
      if(!$folder_id) {
         $this->setError("操作失败！");
         return false;
      }
   
      if($save) {
         foreach ($jobids as $jobid) {
            $values[] = "(" . $folder_id . ", " . $jobid . ")";
         }
         $query = "INSERT IGNORE INTO *PREFIX*hr_job_folder_files (folder_id, job_id) VALUES " . implode(",", $values);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("保存失败!");
            return false;
         }
      } else {
         $query = "DELETE FROM *PREFIX*hr_job_folder_files WHERE folder_id=" . $folder_id . " AND job_id IN (" . implode(",", $jobids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("取消保存失败!");
            return false;
         }
      }
   
      // update total number of saved folder
      $query = "UPDATE *PREFIX*hr_job_folder AS jf SET jf.num=(SELECT COUNT(*) FROM  *PREFIX*hr_job_folder_files WHERE folder_id=" . $folder_id . ")
                WHERE jf.folder_id=" . $folder_id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(OC_User::getUser(), array('#__jobs_job', '*PREFIX*hr_job_folder', '*PREFIX*hr_job_folder_files', '*PREFIX*preferences'));
      foreach($jobids as $jobid) {
         CacheHelper::triggerRemove(OC_User::getUser(), array('#__jobs_job-'.$jobid));
      }
      return true;
   }
   
   public function settop($settop=true) {
      $jobids = json_decode(JRequest::getString("ids", ""));
      if(empty($jobids))
         return true;
       
      JArrayHelper::toInteger($jobids);
      // check job owner
      $jobids = self::checkPermissions($jobids, false);
      if(empty($jobids)) {
         $this->setError("您无权访问该资源");
         return false;
      }
       
      $db = PFactory::getDbo();
       
      $values = array();
      $folder_id = PJobpositionHelper::getSettopFolderID();
      if(!$folder_id) {
         $this->setError("操作失败！");
         return false;
      }
       
      if($settop) {
         foreach ($jobids as $jobid) {
            $values[] = "(" . $folder_id . ", " . $jobid . ", UTC_TIMESTAMP())";
         }
         $query = "INSERT IGNORE INTO *PREFIX*hr_job_folder_files (folder_id, job_id, created) VALUES " . implode(",", $values);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("保存失败!");
            return false;
         }
      } else {
         $query = "DELETE FROM *PREFIX*hr_job_folder_files WHERE folder_id=" . $folder_id . " AND job_id IN (" . implode(",", $jobids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("取消保存失败!");
            return false;
         }
      }
       
      // update total number of settop folder
      $query = "UPDATE *PREFIX*hr_job_folder AS jf SET jf.num=(SELECT COUNT(*) FROM  *PREFIX*hr_job_folder_files WHERE folder_id=" . $folder_id . ")
                WHERE jf.folder_id=" . $folder_id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
       
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(OC_User::getUser(), array('#__jobs_job', '*PREFIX*hr_job_folder', '*PREFIX*hr_job_folder_files'));
      foreach($jobids as $jobid) {
         CacheHelper::triggerRemove(OC_User::getUser(), array('#__jobs_job-'.$jobid));
      }
      return true;
   }
   
   public function trash($trash=true) {
      $jobids = json_decode(JRequest::getString("ids", ""));
      if(empty($jobids))
         return true;
   
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
   
      JArrayHelper::toInteger($jobids);
      // check job admin permission
      $jobids = self::checkPermissions($jobids);
      if(empty($jobids)) {
         $this->setError("您无权访问该资源");
         return false;
      }
   
      if ($trash) {
         // put the job to be trashed, we will not do real remove
         $query = "UPDATE #__jobs_job set is_trashed=1, date_trashed=UTC_TIMESTAMP() WHERE id IN (" . implode(",", $jobids) . ") AND employer_id = " . $user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("删除失败!");
            return false;
         }
      } else {
         $query = "UPDATE #__jobs_job set is_trashed=0, date_trashed=UTC_TIMESTAMP() WHERE id IN (" . implode(",", $jobids) . ") AND employer_id = " . $user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("恢复失败!");
            return false;
         }
      }
   
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove($user->id, array('#__jobs_job'));
      CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_job_folder', '*PREFIX*hr_job_folder_files'));
      foreach($jobids as $jobid) {
         CacheHelper::triggerRemove($user->id, array('#__jobs_job-'.$jobid));
      }
   
      $db->stayOnMaster();
      // get related job folderids
      $query = "SELECT DISTINCT folder_id FROM *PREFIX*hr_job_folder_files WHERE job_id IN (" . implode(",", $jobids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $relatedJobFolderids = $db->loadAssocList('', 'folder_id');
   
      // update total number of related folder
      if (!empty($relatedJobFolderids)) {
         $query = "UPDATE *PREFIX*hr_job_folder AS jf
                   SET jf.num=(SELECT COUNT(f.job_id) FROM  *PREFIX*hr_job_folder_files AS f LEFT JOIN #__jobs_job AS j ON j.id=f.job_id WHERE f.folder_id=jf.folder_id AND j.is_trashed=0)
                   WHERE jf.folder_id IN (" . implode(",", $relatedJobFolderids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
          
         CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_job_folder', '*PREFIX*hr_job_folder_files'));
      }
      
      // get related resume folderids
      $query = "SELECT DISTINCT folder_id FROM *PREFIX*hr_resume_folder_files AS rff
                LEFT JOIN *PREFIX*hr_jobseeker AS j ON j.id=rff.jobseeker_id
                WHERE j.job_id IN (" . implode(",", $jobids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $relatedResumeFolderids = $db->loadAssocList('', 'folder_id');
      
      // update total number of related folder
      if (!empty($relatedResumeFolderids)) {
         $query = "UPDATE *PREFIX*hr_resume_folder AS rf
                   SET rf.num=(SELECT COUNT(f.jobseeker_id) FROM  *PREFIX*hr_resume_folder_files AS f 
                               LEFT JOIN *PREFIX*hr_jobseeker AS j ON j.id=f.jobseeker_id 
                               LEFT JOIN #__jobs_job AS jj ON jj.id=j.job_id 
                               WHERE f.folder_id=rf.folder_id AND j.is_trashed=0 AND jj.is_trashed=0)
                   WHERE rf.folder_id IN (" . implode(",", $relatedResumeFolderids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
          
         CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_resume_folder', '*PREFIX*hr_resume_folder_files'));
      }
   
      // update resume number of company
      $query = "UPDATE #__jobs_employer AS je SET je.num_jobseekers=
                (SELECT COUNT(j.id) FROM  *PREFIX*hr_jobseeker AS j LEFT JOIN #__jobs_job AS jj ON jj.id=j.job_id WHERE j.is_trashed=0 AND jj.is_trashed=0 AND j.company_id=je.user_id)
                WHERE je.user_id=" . $user->id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
   
      CacheHelper::triggerRemove($user->id, array('#__jobs_employer'));
      $db->leaveOnMaster();
   
      return true;
   }
   
   public function checkSubaccountPostJobLocation($job_title,$location_ids){
      $db=PFactory::getDbo();
      $user=PFactory::getMasterUser();      
      //save exist location
      $subuser = PCompanyHelper::getSubUser();
      if($subuser->permissions & PERMISSION_JOB_ADMIN_ALL) {
         return null;
      } else {
         $existarea = array();
         $query ="SELECT jj.state,jj.id_state,jj.city,jj.id_city FROM #__jobs_job AS jj
               LEFT JOIN *PREFIX*hr_jobmanager AS hj ON hj.jobid = jj.id
               WHERE jj.employer_id = ".intval($user->id)." AND jj.job_title=".$db->quote($job_title)."
               AND jj.is_active='y' AND jj.id_city IN(".implode(",", $location_ids).")
               AND jj.id NOT IN (SELECT jobid FROM *PREFIX*hr_jobmanager WHERE manager=".$db->quote(OC_User::getUser() ).")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $joblists = $db->loadAssocList();
         if(count($joblists)>0){
            foreach ($joblists as $list){
               if($list['state'] == $list['city']){
                  $existarea[] = $list['state'];
               }else{
                  $existarea[]=$list['state'].$list['city'];
               }
            }
            return $existarea;
         }else{
            return null;
         }
      }
   }

   public function renewJob(){
      if(!PSession::checkToken()) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      $user = PFactory::getMasterUser();
      $db=PFactory::getDbo();

      $jobids = JRequest::getString('jobids',''); 
      $jobids = explode(',',$jobids);
      JArrayHelper::toInteger($jobids);
      if(empty($jobids)){
         $this->setError("您无权访问该资源");
         return false;
      }

      $jobpermissionids = self::checkPermissions($jobids);

      if(empty($jobpermissionids)) {
         $this->setError("您无权访问该资源");
         return false;
      }

      list($starttime, $endtime) = PResumesHelper::getTimeRange();
      $todaystart = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
      $query = "UPDATE #__jobs_job SET renew = UTC_TIMESTAMP WHERE id IN (" . implode(',', $jobids) . ") AND renew < ".$db->quote($todaystart)." AND employer_id = ".intval($user->id) ;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()){
         $this->setError('网络异常，请刷新页面重试');
         return false;
      }else{
         $query = "SELECT id,renew FROM #__jobs_job WHERE id IN (" . implode(',', $jobids) . ") AND employer_id = ".intval($user->id) ;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $result = $db->loadAssocList();
         foreach ($result as $k=>$v){
            $result[$k]['renew'] = JHtml::_('date',$v['renew'],'Y-m-d');
         }
      }

      CacheHelper::triggerRemove($user->id, array('#__jobs_job'));
      return $result; 
   }
}
